import pandas as pd
from pymysql.converters import escape_string

import pymysql
import asyncio
import requests
import json
import time

# 打开数据库连接
db_config = {
    'host': 'dc.unifound.net', #主机
    'user': 'root',       #用户名
    'password': 'zjdx20210126',  #密码
    'port': 63307,         #端口 3306
    'database': 'org_base_1FE'   #数据库名
}

async def main():
    db = pymysql.connect(**db_config)
    # 使用cursor()方法获取操作游标a
    cursor = db.cursor()
    # SQL 查询语句
    sql = "select uuid,card_id,`name` from b_user where identity_name='教师' and `name` is NOT NULL;"
    try:
        # 执行SQL语句
        cursor.execute(sql)
        # 获取所有记录列表
        results = cursor.fetchall()
        for row in results:
            uuid = row[0]
            card_id = row[1]
            name = row[2]
            data = {
                'k': '浙江理工大学 '+name+''
            }
            # 发送POST请求，并将数据以`form-data`格式提交
            try:
                response = requests.post('http://115.236.46.135/old/searchexpertnew', data=data, timeout=60)
                response_json = json.loads(response.text.strip('\ufeff'))
                if response.status_code == 200:
                    time.sleep(0.5)
                    # 访问 JSON 中的具体字段
                    obj = response_json['data']['obj']
                    if len(obj) > 0:
                        keyword = obj[0]['keyword']
                        if keyword != "" and keyword is not None:
                            insert_query = "INSERT INTO user_tags_for_newbook(uuid,card_id,tags,status,last_time,is_handle) VALUES (%s, %s, %s, 1, now(), 1);"
                            cursor.execute(insert_query, (uuid, card_id, keyword))
                            db.commit()
            except Exception as e:
                print(data)
                print(e)
    except Exception as e:
        print("程序终止")
        print(e)
    # 关闭数据库连接
    db.close()

if __name__ == '__main__':
    loop = asyncio.get_event_loop()
    loop.run_until_complete(main())
